package com.actionsky.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.sphx.api.SphinxClient;
import org.sphx.api.SphinxException;
import org.sphx.api.SphinxResult;

import com.actionsky.entity.Article;
import com.actionsky.entity.SearchMatch;
import com.actionsky.entity.SearchResult;

public class SearchDAO {
	private static Log _log = LogFactory.getLog(SearchDAO.class);
	
	/*根据不包含空格的关键字查询数据*/
	public static List<Article> findArticlesByTitle(String keyword,int int_page_number){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<Article>();
        int int_page_size = 10;			//每页显示10条结果
        String sql = "select id,title,content,content_notags from article where title like '%"+keyword+"%' limit ?,?";
        try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, int_page_number);
			pst.setInt(2, int_page_size);
			rs = pst.executeQuery();
			while(rs.next()){
				Article article = new Article();
				article.setId(rs.getInt("id"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));
				article.setContent_notags(rs.getString("content_notags"));
				list.add(article);
			}
			return list;
		} catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return null;
	}
	
	/*根据包含空格的关键字数组查询数据*/
	public static List<Article> findArticlesByTitle(String[] keywords,int int_page_number){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<Article>();
        int int_page_size = 10;			//每页显示10条结果
        int keywords_len = keywords.length;
        String sql = "select id,title,content,content_notags from article where title like '%"+keywords[0]+"%'";
        for(int i = 1; i < keywords_len; i++){
        	sql += " or title like '%"+keywords[i]+"%'";
        }
        sql += " limit ?,?";
        
        try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, int_page_number);
			pst.setInt(2, int_page_size);
			rs = pst.executeQuery();
			while(rs.next()){
				Article article = new Article();
				article.setId(rs.getInt("id"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));
				article.setContent_notags(rs.getString("content_notags"));
				list.add(article);
			}
			return list;
		} catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return null;
	}
	
	/*根据不包含空格的关键字查询数据总条数*/
	public static int countByTitle(String keyword){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
        ResultSet rs = null;
        int count = 0;
        String sql = "select count(*) from article where title like '%"+keyword+"%'";
        try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return count;
	}
	
	/*根据包含空格的关键字数组查询数据总条数*/
	public static int countByTitle(String[] keywords){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
        ResultSet rs = null;
        int count = 0;
        int keywords_len = keywords.length;
        String sql = "select count(*) from article where title like '%"+keywords[0]+"%'";
        for(int i = 1; i < keywords_len; i++){
        	sql += " or title like '%"+keywords[i]+"%'";
        }
        try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			if(rs.next()){
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return count;
	}
	
	/*根据id查找文章*/
	public static Article findArticleById(int id){
		Connection con = ActionskyUtil.getConnection();
		PreparedStatement pst = null;
        ResultSet rs = null;
        Article article = new Article();
        String sql = "select id,title,content from article where id=?";
        try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			if(rs.next()){
				article.setId(rs.getInt("id"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));
			}
		} catch (SQLException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return article;
	}
	
	/**
	 * Sphinx Full Text DAO
	 * @param q 搜索关键词
	 * 		  group 过滤条件(对应testdoc.gid)
	 *        start 从第几条开始搜索
	 *        end 搜索几条
	 * **/
	public static List<SearchResult> searchFullText(String q, int group, int start, int end){
		Connection con = ActionskyUtil.getConnectionFromSearchServer();
		PreparedStatement pst = null;
        ResultSet rs = null;
        List<SearchResult> list = new ArrayList<SearchResult>();
        SphinxClient sphinxClient = new SphinxClient("www.etuan.com", 9312);
        Map opts = new HashMap();
		opts.put("before_match", "<font color='red'>");
		opts.put("after_match", "</font>");
		opts.put("chunk_separator", " ... ");
		opts.put("limit", 30);	
		opts.put("around", 5);
		String sql = "";
		if(group == 0)
			sql = "SELECT * FROM testdoc where match(?) limit ?,?";
		else if(group == 1)
			sql = "SELECT * FROM testdoc where gid=1 and match(?) limit ?,?";
		else if(group ==2)
			sql = "SELECT * FROM testdoc where gid=2 and match(?) limit ?,?";
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, q);
			pst.setInt(2, start);
			pst.setInt(3, end);
			rs = pst.executeQuery();
			while(rs.next()){
				SearchResult sr = new SearchResult();
				sr.setId(rs.getInt(1));
				sr.setWeight(rs.getInt(2));
				sr.setGid(rs.getInt(3));
				String[] docs = { rs.getString(5),rs.getString(4)};
				String[] buildExcerpts = sphinxClient.buildExcerpts(docs, "testdoc", q, opts);
				sr.setTitle(buildExcerpts[0]);
				sr.setContent(buildExcerpts[1]);
				sr.setLink(rs.getString(6));
				list.add(sr);
			}
			return list;
		} catch (Exception e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		}finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (pst != null) {
                    pst.close();
                    pst = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException ex) {
            }
        }
        return null;
	}
	
	/**
	 * 统计搜索匹配情况
	 **/
	public static SearchMatch matchsTotle(String q){
		SphinxClient cl = ActionskyUtil.getSphinxClient();
		String index = "testdoc";
		SearchMatch sm = null;
		try {
			SphinxResult res = cl.query(q.toString(), index);
			if ( res==null )
			{
				_log.error("Error: " + cl.getLastError());
			}
			if ( cl.getLastWarning()!=null && cl.getLastWarning().length()>0 )
				_log.warn("WARNING: " + cl.getLastWarning());
			sm = new SearchMatch();
			sm.setRetrieved(res.total);
			sm.setMatches(res.totalFound);
			sm.setTime(res.time);
		} catch (SphinxException e) {
			_log.error("查询数据失败!",e);
			e.printStackTrace();
		}
        return sm;
	}
}
